/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.hometown.dao;

import com.hometown.bean.Information;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ye Junbang
 */
public class JDBC {
    static String url = "jdbc:mysql://localhost:3306/registration?useUnicode=true&characterEncoding=utf8";
    static String user = "root";
    static String password = "123456"; 
    
    public static boolean addStudent(String name,String clazz,String number,String phone,String email,String work,String resume){
            Connection con = null;
            Statement stat = null;
            try{
                Class.forName("com.mysql.jdbc.Driver") ; 
                con = DriverManager.getConnection(url,user,password);
                String sql = "insert into registration values(null,'"+name+"','"+clazz+"','"+number+"','"+phone+"','"+email+"','"+work+"','"+resume+"')";
                stat = con.createStatement();
                stat.executeUpdate(sql);
            }catch(ClassNotFoundException e){   
                System.out.println("找不到驱动程序类 ，加载驱动失败！");   
                e.printStackTrace() ;   
                return false;
            }catch(SQLException se){   
                System.out.println("数据库连接失败！");   
                se.printStackTrace() ;   
                return false;
            }finally{
                 try
                 {
                      stat.close();
                 }catch(Exception e)
                 {
                 }
                 try
                 {
                      con.close();
                 }catch(Exception e){
                 }
            }   
           return true;
        }
    public static List listOfApplicants(){
        Connection con = null;
        Statement stat = null;
        ResultSet result = null;
        List<Information> list = new ArrayList<Information>();
            try{
                Class.forName("com.mysql.jdbc.Driver") ; 
                con = DriverManager.getConnection(url,user,password);
                String sql = "select * from registration";
                stat = con.createStatement();
                result = stat.executeQuery(sql);
               
                while(result.next()){
                    Information info = new Information();
                    info.setName(result.getString(2));
                    info.setClazz(result.getString(3));
                    info.setNumber(result.getString(4));
                    info.setPhone(result.getString(5));
                    info.setEmail(result.getString(6));
                    info.setWork(result.getString(7));
                    list.add(info);
                }   
            
            }catch(ClassNotFoundException e){   
                System.out.println("找不到驱动程序类 ，加载驱动失败！");   
                e.printStackTrace() ;   
                return null;
            }catch(SQLException se){   
                System.out.println("数据库连接失败！");   
                se.printStackTrace() ;   
                return null;
            }finally{
                try
                 {
                      result.close();
                 }catch(Exception e)
                 {
                 }
                 try
                 {
                      stat.close();
                 }catch(Exception e)
                 {
                 }
                 try
                 {
                      con.close();
                 }catch(Exception e){
                 }
            }   
           return list;
    } 
    public static List listOfChoice(String choiceWork){
        Connection con = null;
        Statement stat = null;
        ResultSet result = null;
        List<Information> listChoice = new ArrayList<Information>();
            try{
                String wo = new String(choiceWork.getBytes("ISO-8859-1"),"utf-8");
          
                Class.forName("com.mysql.jdbc.Driver") ; 
                con = DriverManager.getConnection(url,user,password);
                String sql = "select * from registration where work='"+wo+"'";
                stat = con.createStatement(); 
                result = stat.executeQuery(sql);
               
                while(result.next()){
                    Information in = new Information();
                    in.setName(result.getString(2));
                    in.setClazz(result.getString(3));
                    in.setNumber(result.getString(4));
                    in.setPhone(result.getString(5)); 
                    in.setEmail(result.getString(6));
                    in.setWork(result.getString(7));
                    listChoice.add(in);
                }   
            
            }catch (UnsupportedEncodingException ex) {
                ex.printStackTrace();
            }catch(ClassNotFoundException e){   
                System.out.println("找不到驱动程序类 ，加载驱动失败！");   
                e.printStackTrace() ;   
                return null;
            }catch(SQLException se){   
                System.out.println("数据库连接失败！");   
                se.printStackTrace() ;   
                return null;
            }finally{
                try
                 {
                      result.close();
                 }catch(Exception e)
                 {
                 }
                 try
                 {
                      stat.close();
                 }catch(Exception e)
                 {
                 }
                 try
                 {
                      con.close();
                 }catch(Exception e){
                 }
            }
           return listChoice;
        }
}
